﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Diagnostics;
using System.IO;
using System.Text;
using Eflong.Utility;

namespace Eflong.DataAccess
{
    public class DatabaseManager : IDisposable
    {
        private string connectionString = "Integrated Security=SSPI;Initial Catalog=;Data Source=localhost;";
        /*          * 机器名          */
        private string hostName = "localhost";

        /*          * 数据库文件路径          */
        private string databaseFilePath;

        /*          * sql 文件路径          */
        private string sqlFilePath;

        /*          * 用户名          */
        private string userName;
        /*          * 密码          */
        private string password;
        /*          * 数据库名          */
        private string dbName;

        private SqlConnection sqlConnection = null;

        private SqlCommand sqlCommand = null;

        public delegate void OutMsgCallback( object sender , ProcessEventArgs e );

        public OutMsgCallback SetMsgCallback;

        public DatabaseManager( string HostName , string DataBaseFilePath , string User , string Password , string SqlFilePath )
        {
            hostName = HostName.Trim( );
            databaseFilePath = DataBaseFilePath.Trim( );
            sqlFilePath = SqlFilePath.Trim( );
            userName = User.Trim( );
            password = Password.Trim( );
            string sqlConnectionCommand = "Data Source=" + HostName + ";Initial Catalog=;User ID=" + userName + ";Pwd=" + password;
            connectionString = sqlConnectionCommand;

            sqlConnection = new SqlConnection( connectionString );
        }

        ~DatabaseManager( )
        {
            Dispose( false );
        }

        protected void Dispose( bool disposing )
        {
            if ( sqlCommand != null )
            {
                sqlCommand.Dispose( );
            }
            try
            {
                if ( sqlConnection != null && sqlConnection.State == ConnectionState.Open )
                {
                    sqlConnection.Close( );
                }
            }
            catch ( Exception ex )
            {
                Debug.WriteLine( ex.Message );
            }
        }

        public void Dispose( )
        {
            Dispose( true );

            GC.SuppressFinalize( this );
        }

        public bool SQL_CreateDatabase( string dateString )
        {
            bool bRet = false;

            // 打开数据库连接

            this.dbName = "KWSystem_HisData" + dateString;
            try
            {
                if ( sqlConnection.State != ConnectionState.Open )
                {
                    sqlConnection.Open( );
                }

                // CREATE DATABASE [KWSystem_HisData20090827]
                //ON (NAME = N'KWSystem_HisData20090827_Data',
                // FILENAME = N'F:\KWSystem\DB\KWSystem_HisData20090827_Data.MDF' , SIZE = 1, FILEGROWTH = 10%)
                // LOG ON (NAME = N'KWSystem_HisData20090827_Log',
                // FILENAME = N'F:\KWSystem\DB\KWSystem_HisData20090827_Log.LDF' , SIZE = 1, FILEGROWTH = 10%)
                string sql = "CREATE DATABASE " + dbName + " ON PRIMARY"
                    + "(NAME=" + dbName + "_Data, FILENAME =N'" + databaseFilePath + "\\" + dbName + "_Data.MDF', SIZE=1, FILEGROWTH=10%)"
                    + "  LOG ON "
                    + "(NAME=" + dbName + "_Log, FILENAME=N'" + databaseFilePath + "\\" + dbName + "_Log.LDF', SIZE=1, FILEGROWTH=10%, maxsize=20)";

                if ( sqlCommand == null )
                {
                    sqlCommand = new SqlCommand( sql , sqlConnection );
                }
                else
                {
                    sqlCommand.CommandText = sql;
                    sqlCommand.Connection = sqlConnection;
                }

                // 产生数据库

                sqlCommand.ExecuteNonQuery( );

                //sql = String.Format("exec sp_addlogin 'kwuser','1234', '{0}';exec sp_addsrvrolemember 'kwuser','sysadmin';use {1};exec sp_adduser 'kwuser','kwuser'", dbName, dbName);
                // 分配 kwuser 用户的权限

                sql = String.Format( "use {0};exec sp_adduser 'kwuser','kwuser'" , dbName );
                sqlCommand.CommandText = sql;

                sqlCommand.ExecuteNonQuery( );

                bRet = true;
            }
            catch ( SqlException ex )
            {
                //MessageBox.Show(ex.Message.ToString());
                if ( SetMsgCallback != null )
                {
                    SetMsgCallback( this , new ProcessEventArgs( "Error!: " + ex.Message ) );
                }
                Debug.WriteLine( ex.Message );
            }
            return bRet;
        }

        ///
        /// 判断数据库是否存在

        /// 通过指定专用的连接字符串，执行一个不需要返回值的SqlCommand命令。

        ///
        ///
        /// 使用示例：

        /// bool databaseExist = SqlHelper.CheckExistsDatebase(dataBaseNameStr);
        ///  数据库名
        ///
        ///
        public bool CheckExistsDatebase( string dataBaseName )
        {
            //string connstring = SqlHelper.ConnectionStringLocalTransaction;
            String dataBaseNameStr = "select count(1) From master.dbo.sysdatabases where name='" + dataBaseName + "'";
            using ( SqlConnection con = new SqlConnection( connectionString ) )
            {
                con.Open( );
                SqlCommand cmd = new SqlCommand( dataBaseNameStr , con );
                int result = Convert.ToInt32( cmd.ExecuteScalar( ) );
                if ( result == 0 )
                {
                    return false;
                }
                else
                {
                    return true;
                }
            }
        }

        /// <summary>
        /// 产生某年某月某天相关的数据表
        /// </summary>
        /// <param name="dateString">"20090826"</param>
        public void SQL_CreateTables( string dateString )
        {
            string strSQLServer = string.Empty;
            try
            {
                // SQL 2000
                strSQLServer = Microsoft.Win32.Registry.LocalMachine.OpenSubKey( @"software\Microsoft\Microsoft SQL Server\80\Tools\ClientSetup" ).GetValue( "SQLPath" ).ToString( );
            }
            catch ( Exception ex )
            {
                Console.WriteLine( ex.Message );
                try
                {
                    // SQL 2005
                    strSQLServer = Microsoft.Win32.Registry.LocalMachine.OpenSubKey( @"software\Microsoft\Microsoft SQL Server\90\Tools\ClientSetup" ).GetValue( "SQLPath" ).ToString( );
                }
                catch ( System.Exception ex1 )
                {
                    Console.WriteLine( ex1.Message );
                    try
                    {
                        strSQLServer = Microsoft.Win32.Registry.LocalMachine.OpenSubKey( @"software\Microsoft\Microsoft SQL Server\100\Tools\ClientSetup\" ).GetValue( "SQLPath" ).ToString( );
                    }
                    catch ( Exception ex2 )
                    {
                        Console.WriteLine( ex2.Message );
                        strSQLServer = @"\\" + hostName;
                        strSQLServer += @"\c$\Program Files\Microsoft SQL Server\80\Tools";
                    }
                }
            }
            strSQLServer = strSQLServer + @"\Binn\osql.exe";

            // \\192.168.0.254\c$\Program Files\Microsoft SQL Server\80\Tools\Binn\osql.exe
            //string dateString = DateTime.Now.ToString("yyyyMMdd", DateTimeFormatInfo.InvariantInfo);

            GenSQLFile( dateString );

            string sqlFile = sqlFilePath + @"\Data\Create_KWSystem_HisData" + dateString + ".sql";
            string para = String.Format( "-U {0} -P {1} -S {2} -i {3}" , userName , password , hostName , sqlFile ); //U为用户名,P为密码,S为目标服务器的ip,str为数据库脚本所在的路径

            RunProcess process = new RunProcess( );
            process.ProcessCompleted += new ProcessCompletedHandler( onProcessCompleted );
            process.ProcessProgress += new ProcessProgressHandler( onProcessProgress );
            process.Run( strSQLServer , para );
            string outString = process.Output;
            string errString = process.Error;
            bool hasErr = process.HasError;
        }

        private void onProcessCompleted( object sender , ProcessEventArgs e )
        {
            throw new NotImplementedException( );
        }

        private void onProcessProgress( object sender , ProcessEventArgs e )
        {
            if ( SetMsgCallback != null )
            {
                SetMsgCallback( this , e );
            }
        }

        /// <summary>
        /// 生成某天的SQL脚本文件
        /// </summary>
        /// <param name="dateString"></param>
        private void GenSQLFile( string dateString )
        {
            string sqlFileForRead = sqlFilePath + @"\Data\Create_KWSystem_HisData.sql";
            string sqlFileForWrite = sqlFilePath + @"\Data\Create_KWSystem_HisData" + dateString + ".sql";
            if ( File.Exists( sqlFileForRead ) == false )
            {
                throw new Exception( "无法读取文件！" );
            }
            if ( File.Exists( sqlFileForWrite ) == false )
            {
            }
            using ( FileStream ws = new FileStream( sqlFileForWrite , FileMode.Create , FileAccess.Write , FileShare.ReadWrite ) )
            {
                StreamWriter writer = new StreamWriter( ws , Encoding.Default );
                writer.WriteLine( "use [KWSystem_HisData" + dateString + "]" );
                writer.WriteLine( "GO" );

                using ( FileStream fs = new FileStream( sqlFileForRead , FileMode.Open , FileAccess.Read , FileShare.Read ) )
                {
                    StreamReader reader = new StreamReader( fs , System.Text.Encoding.Default );
                    try
                    {
                        do
                        {
                            writer.WriteLine( reader.ReadLine( ) );
                        }
                        while ( reader.Peek( ) != -1 );
                    }

                    finally
                    {
                        reader.Close( );
                    }
                }
                writer.Close( );
            }
        }
    }
}